Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports Npgsql

Namespace Persistencia.ModuloPrincipal

    Public Class bd

#Region "Atributos"

        Private Shared instance As bd
        Private conn As NpgsqlConnection
        Private stringConnection As String = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=golden;Database=bdmsh;"

#End Region

        'Constructor
        Public Sub New()
            Try
                conn = New NpgsqlConnection(stringConnection)
            Catch ex As NpgsqlException
                MsgBox(ex)
            End Try
        End Sub

#Region "getInstance"
        'Patron Singleton 
        Public Shared Function getInstance() As bd

            If instance Is Nothing Then
                instance = New bd
            End If

            Return instance

        End Function
#End Region

#Region "funciones"
        Private Sub Open()
            conn.Open()
        End Sub

        Private Sub Close()
            conn.Close()
        End Sub


        Public Function EjecutarConRetorno(ByVal query As String) As Hashtable
            Dim array As New Hashtable
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()

                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()

                Dim i As Integer
                While dr.Read()
                    For i = 0 To dr.FieldCount - 1
                        Dim dato As String = CStr(dr(dr.GetName(i).ToString))
                        Dim nombrecampo As String = dr.GetName(i).ToString
                        array.Add(nombrecampo, dato)
                    Next i
                End While

                Me.Close()

                Return array
            Catch ex As NpgsqlException
                MsgBox(ex.ToString)
            End Try
            Return Nothing

        End Function

        Public Function ObtenerUsuario(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Usuario
                While dr.Read()
                    Objeto = New Usuario(CStr(dr("usuario")), CStr(dr("password")))
                    Objeto.RefEmpleado = CStr(dr("fkempleado"))
                    Objeto.Grupo = CStr(dr("fkgrupousuarios"))
                    Objeto.ID = CStr(dr("id"))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                MsgBox(ex.ToString)
            End Try
            Return Nothing
        End Function

        Public Function ObtenerGrupo(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As GrupoUsuario
                While dr.Read()
                    Objeto = New GrupoUsuario(CStr(dr("id")), CStr(dr("nombregrupo")), CStr(dr("accesoadministracion")), CStr(dr("accesoinformes")), CStr(dr("accesovistapanol")), CStr(dr("accesobodega")), _
                                            CStr(dr("accesoindicadores")), CStr(dr("accesobusqueda")), CStr(dr("accesorecursoshumanos")), CStr(dr("accesoplanificacion")), CStr(dr("accesocontabilidad")), _
                                            CStr(dr("accesosistema")), CStr(dr("accesosistemageneral")), CStr(dr("accesosolicitudbodega")), CStr(dr("accesosistemapersonal")))
                    array.Add(Objeto)
                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                MsgBox(ex.ToString)
            End Try
            Return Nothing

        End Function

        Public Function obtenerEmpleado(ByVal query As String) As ArrayList
            Dim array As New ArrayList
            Dim dr As Npgsql.NpgsqlDataReader
            Try
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Empleado
                While dr.Read()
                    Objeto = New Empleado(CStr(dr("nombre")), CStr(dr("apellido")), _
                    CStr(dr("rut")), _
                    CStr(dr("direccion")), _
                    CStr(dr("telefono")), _
                    CStr(dr("profesion")), _
                    CStr(dr("edad")), _
                    CStr(dr("fechainicioempresa")), _
                    CStr(dr("fechafinempresa")), _
                    CStr(dr("sexo")), _
                    CStr(dr("fechanacimiento")), _
                    CStr(dr("nacionalidad")), _
                    CStr(dr("paisnacimiento")), _
                    CStr(dr("regionnacimiento")), _
                    CStr(dr("comunanacimiento")), _
                    CStr(dr("ciudadnacimiento")), _
                    CStr(dr("provinciaresidencia")), _
                    CStr(dr("ciudadresidencia")), _
                    CStr(dr("celular")), _
                    CStr(dr("email")), _
                    CStr(dr("estadocivil")), _
                    CStr(dr("cargasfamiliares")), _
                    CStr(dr("numerocalzado")), _
                    CStr(dr("talla")), CStr(dr("rutafoto")))
                    array.Add(Objeto)

                End While
                Me.Close()
                Return array
            Catch ex As NpgsqlException
                MsgBox(ex.ToString)
            End Try
            Return Nothing
        End Function

     
#End Region

    End Class

End Namespace

